package channel;
import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBElement;
import javax.xml.bind.Unmarshaller;

import UCSDataXML.ComputeBlade;
import UCSDataXML.ConfigResolveClasses;
import UCSDataXML.EquipmentChassis;
import UCSDataXML.EquipmentFanModule;
import UCSDataXML.EquipmentPsu;
import UCSDataXML.NetworkElement;
import UCSDataXML.ObjectFactory;
import UCSDataXML.OutConfigs;
import UCSDataXML.ServiceProfile;



public class UCSDataUnmarshal 
{
	Connection con=null;
	PreparedStatement pst=null,pst2=null,pst3=null,pst4=null,pst5=null,pst6=null,pst7=null;
	Statement s=null;
	ResultSet rs=null;
	public UCSDataUnmarshal(String filename)
	{
		connect();
		parse(filename);
	}
	public void connect()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/project", "root", "dasta99");
			System.out.println("Connection established..!");
			DatabaseMetaData metadata=con.getMetaData();
			//EquipmentChasis Details..
			ResultSet rsr=metadata.getTables(null, null, "equipmentChasis", null);
			if(rsr.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table equipmentChasis");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table equipmentChasis(dn varchar(40),id varchar(40),adminState varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),power varchar(40),operability varchar(40),operState varchar(40),configState varchar(40),fabricEpDn varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				PreparedStatement p=con.prepareStatement("delete from equipmentChasis");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement pos=con.prepareStatement("create table equipmentChasis(dn varchar(40),id varchar(40),adminState varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),power varchar(40),operability varchar(40),operState varchar(40),configState varchar(40),fabricEpDn varchar(40))");
				pos.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			//EquipmentFanModule Details..
			ResultSet rsr1=metadata.getTables(null, null, "equipmentFanModule", null);
			if(rsr1.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table equipmentFanModule");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table equipmentFanModule(rn varchar(40),id varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),presence varchar(40),power varchar(40),operability varchar(40),operState varchar(40),voltage varchar(40),tray varchar(40),perf varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				PreparedStatement p=con.prepareStatement("delete from equipmentFanModule");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement pos=con.prepareStatement("create table equipmentFanModule(rn varchar(40),id varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),presence varchar(40),power varchar(40),operability varchar(40),operState varchar(40),voltage varchar(40),tray varchar(40),perf varchar(40))");
				pos.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			//ComputeBlade DEtails..
			ResultSet rsr2=metadata.getTables(null, null, "computeBlade", null);
			if(rsr2.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table computeBlade");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table computeBlade(rn varchar(40),intId varchar(40),lc varchar(40),adminState varchar(40),vendor varchar(40),model varchar(40),uuid varchar(70),serial varchar(40),operability varchar(40),operState varchar(40),connStatus varchar(40),connPath varchar(40),slotId varchar(40),chasisId varchar(40),totalMemory varchar(40),originalUuid varchar(70),operPower varchar(40),numOfThreads varchar(40),numOfFcHostIfs varchar(40),numOfEthHostIfs varchar(40),numOfCpus varchar(40),numOfCoresEnabled varchar(40),numOfAdaptors varchar(40),discovery varchar(40),checkPoint varchar(40),availableMemory varchar(40),availability varchar(40),adminPower varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				
				PreparedStatement p=con.prepareStatement("delete from computeBlade");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement ps=con.prepareStatement("create table computeBlade(rn varchar(40),intId varchar(40),lc varchar(40),adminState varchar(40),vendor varchar(40),model varchar(40),uuid varchar(70),serial varchar(40),operability varchar(40),operState varchar(40),connStatus varchar(40),connPath varchar(40),slotId varchar(40),chasisId varchar(40),totalMemory varchar(40),originalUuid varchar(70),operPower varchar(40),numOfThreads varchar(40),numOfFcHostIfs varchar(40),numOfEthHostIfs varchar(40),numOfCpus varchar(40),numOfCoresEnabled varchar(40),numOfAdaptors varchar(40),discovery varchar(40),checkPoint varchar(40),availableMemory varchar(40),availability varchar(40),adminPower varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			//EquipmentPsu Details..
			ResultSet rsr3=metadata.getTables(null, null, "equipmentPsu", null);
			if(rsr3.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table equipmentPsu");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table equipmentPsu(rn varchar(40),id varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),presence varchar(40),power varchar(40),operability varchar(40),operState varchar(40),voltage varchar(40),perf varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				
				PreparedStatement p=con.prepareStatement("delete from equipmentPsu");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement ps=con.prepareStatement("create table equipmentPsu(rn varchar(40),id varchar(40),vendor varchar(40),model varchar(40),thermal varchar(40),serial varchar(40),presence varchar(40),power varchar(40),operability varchar(40),operState varchar(40),voltage varchar(40),perf varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			ResultSet rsr4=metadata.getTables(null, null, "lsServer", null);
			if(rsr4.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table lsServer");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table lsServer(name varchar(40),dn varchar(40),childAction varchar(40),intId varchar(40),type varchar(40),uuid varchar(40),operState varchar(40),configState varchar(40),pnDn varchar(40),owner varchar(40),operStatsPolicyName varchaR(40),operScrubPolicyName varchar(40),operLocalDiskPolicy varchar(40),operBootPolicyName varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				
				PreparedStatement p=con.prepareStatement("delete from lsServer");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement ps=con.prepareStatement("create table lsServer(name varchar(40),dn varchar(40),childAction varchar(40),intId varchar(40),type varchar(40),uuid varchar(40),operState varchar(40),configState varchar(40),pnDn varchar(40),owner varchar(40),operStatsPolicyName varchaR(40),operScrubPolicyName varchar(40),operLocalDiskPolicy varchar(40),operBootPolicyName varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			//NetworkElement Details..
			ResultSet rsr5=metadata.getTables(null, null, "networkElement", null);
			if(rsr5.next())
			{
				/*PreparedStatement p=con.prepareStatement("drop table networkElement");
				p.executeUpdate();
				System.out.println("Table dropped..!!");
				
				PreparedStatement ps=con.prepareStatement("create table networkElement(vendor varchar(40),totalMemory varchar(40),thermal varchar(40),serial varchar(40),revision varchar(40),operability varchar(40),oobIfMask varchar(40),oobIfIp varchar(40),oobIfGw varchar(40),model varchar(40),inventoryStatus varchar(40),inbandIfVnet varchar(40),inbandIfMask varchar(40),inbandIfIp varchar(40),inbandIfGw varchar(40),id varchar(40),fltAggr varchar(40),dn varchar(40),childAction varchar(40),adminInbandIfState varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");*/
				
				PreparedStatement p=con.prepareStatement("delete from networkElement");
				p.executeUpdate();
			}
			else
			{
				PreparedStatement ps=con.prepareStatement("create table networkElement(vendor varchar(40),totalMemory varchar(40),thermal varchar(40),serial varchar(40),revision varchar(40),operability varchar(40),oobIfMask varchar(40),oobIfIp varchar(40),oobIfGw varchar(40),model varchar(40),inventoryStatus varchar(40),inbandIfVnet varchar(40),inbandIfMask varchar(40),inbandIfIp varchar(40),inbandIfGw varchar(40),id varchar(40),fltAggr varchar(40),dn varchar(40),childAction varchar(40),adminInbandIfState varchar(40))");
				ps.executeUpdate();
				System.out.println("Table created successfully..!");
			}
			
			String query="insert into equipmentChasis values(?,?,?,?,?,?,?,?,?,?,?,?)";
			pst=con.prepareStatement(query);
			pst2=con.prepareStatement("insert into equipmentFanModule values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pst3=con.prepareStatement("insert into computeBlade values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pst4=con.prepareStatement("insert into equipmentPsu values(?,?,?,?,?,?,?,?,?,?,?,?)");
			//pst5=con.prepareStatement("insert into equipmentFan values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pst6=con.prepareStatement("insert into networkElement values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pst7=con.prepareStatement("insert into lsServer values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public void parse(String filename)
	{
		try
		{
		
			File file=new File(filename);
			JAXBContext jcontext=JAXBContext.newInstance(ObjectFactory.class);
			Unmarshaller umarsh=jcontext.createUnmarshaller();
			@SuppressWarnings("unchecked")
			JAXBElement<ConfigResolveClasses> rootElement=(JAXBElement<ConfigResolveClasses>)umarsh.unmarshal(file);
			
			ConfigResolveClasses config=rootElement.getValue();
			OutConfigs outconfig=config.getOutConfigs();
			List<EquipmentChassis> chasis=outconfig.getEquipmentChassis();
			System.out.println("ChasisData:");
		
			for(EquipmentChassis chass:chasis)
			{
			
				pst.setString(1, chass.getDn());
				pst.setString(2, chass.getId());
				pst.setString(3, chass.getAdminState());
				pst.setString(4, chass.getVendor());
				pst.setString(5, chass.getModel());
				pst.setString(6, chass.getThermal());
				pst.setString(7, chass.getSerial());
				pst.setString(8, chass.getPower());
				pst.setString(9, chass.getOperability());
				pst.setString(10, chass.getOperState());
				pst.setString(11, chass.getConfigState());
				pst.setString(12, chass.getFabricEpDn());
				pst.executeUpdate();
			
				List<EquipmentFanModule> fan=chass.getEquipmentFanModule();
				System.out.println("FanModuleData:");
				for(EquipmentFanModule fans:fan)
				{
					pst2.setString(1, fans.getRn());
					pst2.setString(2, fans.getId());
					pst2.setString(3, fans.getVendor());
					pst2.setString(4, fans.getModel());
					pst2.setString(5, fans.getThermal());
					pst2.setString(6, fans.getSerial());
					pst2.setString(7, fans.getPresence());
					pst2.setString(8, fans.getPower());
					pst2.setString(9, fans.getOperability());
					pst2.setString(10, fans.getOperState());
					pst2.setString(11, fans.getVoltage());
					pst2.setString(12, fans.getTray());
					pst2.setString(13, fans.getPref());
					pst2.executeUpdate();
					//List<EquipmentFan> faaa=fans.getEquipmentFan();
					//System.out.println("EqipmentFan Data:");
					/*for(EquipmentFan fa:faaa)
					{
						pst5.setString(1, fa.getVendor());
						pst5.setString(2, fa.getSerial());
						pst5.setString(3, fa.getRevision());
						pst5.setString(4, fa.getPresence());
						pst5.setString(5, fa.getOperability());
						pst5.setString(6, fa.getOperState());
						pst5.setString(7, fa.getModel());
						pst5.setString(8, fa.getFltAggr());
						pst5.setString(9, fa.getChildAction());
						pst5.setString(10, fa.getRn());
						pst5.setString(11, fa.getId());
						pst5.setString(12, fa.getVoltage());
						pst5.setString(13, fa.getThermal());
						pst5.setString(14, fa.getPerf());
						pst5.setString(15, fa.getOpeQualifierReason());
						pst5.setString(16, fa.getTray());
						pst5.setString(17, fa.getModule());
						pst5.setString(18, fa.getIntType());
						pst5.executeUpdate();
					}*/
				}
				
				List<ComputeBlade> blade=chass.getComputeBlade();
				System.out.println("BladeData:");
				for(ComputeBlade blades:blade)
				{
					pst3.setString(1, blades.getRn());
					pst3.setString(2, blades.getIntId());
					pst3.setString(3, blades.getLc());
					pst3.setString(4, blades.getAdminState());
					pst3.setString(5, blades.getVendor());
					pst3.setString(6, blades.getModel());
					pst3.setString(7, blades.getUuid());
					pst3.setString(8, blades.getSerial());
					pst3.setString(9, blades.getOperability());
					pst3.setString(10, blades.getOperState());
					pst3.setString(11, blades.getConnStatus());
					pst3.setString(12, blades.getConnPath());
					pst3.setString(13, blades.getSlotTd());
					pst3.setString(14, blades.getChassisId());
					pst3.setString(15, blades.getTotalMemory());
					pst3.setString(16, blades.getOriginalUuid());
					pst3.setString(17, blades.getOperPower());
					pst3.setString(18, blades.getNumOfThreads());
					pst3.setString(19, blades.getNumOfHostIfs());
					pst3.setString(20, blades.getNumOfEthHostIfs());
					pst3.setString(21, blades.getNumOfCpus());
					pst3.setString(22, blades.getNumOfCoresEnabled());
					pst3.setString(23, blades.getNumOfAdaptors());
					pst3.setString(24, blades.getDiscovery());
					pst3.setString(25, blades.getCheckPoint());
					pst3.setString(26, blades.getAvailableMemory());
					pst3.setString(27, blades.getAvailability());
					pst3.setString(28, blades.getAdminPower());
					pst3.executeUpdate();
				}
				List<EquipmentPsu> psu=chass.getEquipmentPsu();
				System.out.println("PSUData:");
				for(EquipmentPsu psus:psu)
				{
					pst4.setString(1, psus.getRn());
					pst4.setString(2, psus.getId());
					pst4.setString(3, psus.getVendor());
					pst4.setString(4, psus.getModel());
					pst4.setString(5, psus.getThermal());
					pst4.setString(6, psus.getSerial());
					pst4.setString(7, psus.getPresence());
					pst4.setString(8, psus.getPower());
					pst4.setString(9, psus.getOperability());
					pst4.setString(10, psus.getOperState());
					pst4.setString(11, psus.getVoltage());
					pst4.setString(12, psus.getPerf());
					pst4.executeUpdate();
					
				}
			}
			List<NetworkElement> net=outconfig.getNetworkElement();
			System.out.println("NetworkFabric Data:");
			for(NetworkElement nn:net)
			{
				pst6.setString(1, nn.getVendor());
				pst6.setString(2, nn.getTotalMemory());
				pst6.setString(3, nn.getThermal());
				pst6.setString(4, nn.getSerial());
				pst6.setString(5, nn.getRevision());
				pst6.setString(6, nn.getOperability());
				pst6.setString(7, nn.getOobIfMask());
				pst6.setString(8, nn.getOobIfIp());
				pst6.setString(9, nn.getOobIfGw());
				pst6.setString(10, nn.getModel());
				pst6.setString(11, nn.getInventoryStatus());
				pst6.setString(12, nn.getInbandIfVnet());
				pst6.setString(13, nn.getInbandIfMask());
				pst6.setString(14, nn.getInbandIfIp());
				pst6.setString(15, nn.getInbandIfGw());
				pst6.setString(16, nn.getId());
				pst6.setString(17, nn.getFltAggr());
				pst6.setString(18, nn.getDn());
				pst6.setString(19, nn.getChildAction());
				pst6.setString(20, nn.getAdminInbandIfState());
				pst6.executeUpdate();
			}
			
			System.out.println("Service Profile Data...!");
			ServiceProfile service=outconfig.getServiceProfile();
			pst7.setString(1, service.getName());
			pst7.setString(2, service.getDn());
			pst7.setString(3, service.getChildAction());
			pst7.setString(4, service.getIntId());
			pst7.setString(5, service.getType());
			pst7.setString(6, service.getUuid());
			pst7.setString(7, service.getOperState());
			pst7.setString(8, service.getConfigState());
			pst7.setString(9, service.getPnDn());
			pst7.setString(10, service.getOwner());
			pst7.setString(11, service.getOperStatsPolicyName());
			pst7.setString(12, service.getOperScrubPolicyName());
			pst7.setString(13, service.getOperLocalDiskPolicy());
			pst7.setString(14, service.getOperBootPolicyName());
			pst7.executeUpdate();
			con.close();
			System.out.println("Data Inserted Successfully....!");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	

}
